<?php
/**
 * Created by PhpStorm.
 * User: fengdh
 * Date: 2019/8/8
 * Time: 14:25
 */

namespace business\company\services;

use business\base\services\BasicCategoryService;
use business\base\services\DictionaryService;
use business\base\services\QualificationService;
use business\base\services\SystemConfigService;
use business\datacenter\models\SupplierHonourScoreModel;
use business\datacenter\models\SupplierRecommendLog;
use business\company\models\SupplierBasicModel;
use business\company\models\SupplierIntroduction;
use business\datacenter\repositories\LandRepository;
use business\search\services\BaseUtil;
use business\user\models\BusinessCardModel;
use business\user\services\UserService;
use common\utils\PinyinUtil;
use search\models\UucSupplierVoteModel;
use yii\helpers\ArrayHelper;
use yii\mongodb\Query;

class SupplierService
{
    const HIGH_QUALITY_FOR_2020_MARK = '高品质供应商';

    public function getSupplierIdsList($time = null, $is_valid = false)
    {
        $query = SupplierBasicModel::find()
            ->select('supplier_id');
        $time && $query->andWhere('last_updated_time >= :time', [':time' => $time]);
        $is_valid && $query->andWhere(['status' => 2, 'state' => 2]);
        $data = $query->column();
        return $data;
    }

    /**
     * 获取供应商基本信息
     * @param $supplier_id
     * @return
     */
    public function getSupplierBasicInfo($supplier_id)
    {
        if (empty($supplier_id)) {
            return null;
        }

        $supplier = SupplierBasicModel::findOne(['supplier_id' => $supplier_id]);
        if (!empty($supplier)) {
            //供应商id
            //$basicInfo['companySupplierId'] = $supplier_id;
            $basicInfo['supplierId'] = $supplier_id;
            //供应商名称
            $basicInfo['companyName'] = $supplier->companyName;
            $basicInfo['shortName'] = $supplier->shortName;
            // 公司工商状态
            $basicInfo['companyStatus'] = $supplier->companyStatus;

            $basicInfo['companyNamePinyin'] = [
                PinyinUtil::getPinyin($basicInfo['companyName']),
                PinyinUtil::getShortPinyin($basicInfo['companyName'])
            ];

            //企业简介
            $supplierIntro = $this->getSupplierIntroductionById($supplier_id);
            $basicInfo['companyIntro'] = (!empty($supplierIntro) && isset($supplierIntro->supplierIntro))
                ? $supplierIntro->supplierIntro : '';
            //业务范围
            $basicInfo['businessScope'] = empty($supplier->businessScope) ? '' : $supplier->businessScope;
            //是否是名企
            $basicInfo['isFamousSupplier'] = empty($supplier->isFamous) ? 0 : intval($supplier->isFamous);
            //是否是金牌
            $basicInfo['isGoldenSupplier'] = empty($supplier->supplierLevel) ? 0 : intval($supplier->supplierLevel);
            //是否是铂金
            $basicInfo['isPlatinumSupplier'] = empty($supplier->platinumFlag) ? 0 : intval($supplier->platinumFlag);
            //是否是钻石
            $basicInfo['isDiamondSupplier'] = empty($supplier->diamondFlag) ? 0 : intval($supplier->diamondFlag);
            $basicInfo['isRegionalDiamond'] = empty($supplier->regionalDiamondLevel) ? 0 : intval($supplier->regionalDiamondLevel);

            //是否是供应商十强
            $basicInfo['isTop'] = empty($supplier->isTop) ? 0 : intval($supplier->isTop);

            //供应商类型: 厂商，工程等
            $dictionaryService = new DictionaryService();
            $basicInfo['supplierType'] = $supplier->supplierType;
            //注册资本
            $basicInfo['regCapital'] = empty($supplier->regCapitalCny) ? 0 : intval($supplier->regCapitalCny);
            //注册资本国际
            $basicInfo['regCapitalNation'] = empty($supplier->regCapital) ? 0 : intval($supplier->regCapital);
            $basicInfo['regCapitalUnit'] = empty($supplier->regCapitalUnit) ? '' : $supplier->regCapitalUnit;
            //服务区域
            $serviceLocationCodes = $this->getServiceLocationCodeListByCompanyId($supplier_id);
            $basicInfo['serviceLocationCode'] = $serviceLocationCodes;
            $serviceLocationCodeStr = is_array($serviceLocationCodes) ? implode(',',
                $serviceLocationCodes) : $serviceLocationCodes;
            $basicInfo['serviceLocationName'] = $dictionaryService->getAreaNameByCodes($serviceLocationCodeStr);
            //成立年限
            $basicInfo['establishYear'] = empty($supplier->establishYear) ? 0 : intval($supplier->establishYear);
            //公司所在地
            $regProvinceCode = empty($supplier->provinceCode) ? [] : [$supplier->provinceCode];
            $regCityCode = empty($supplier->cityCode) ? [] : [$supplier->cityCode];
            $basicInfo['regLocationCode'] = array_merge($regProvinceCode, $regCityCode);
            $basicInfo['regLocationName'] = empty($supplier->address) ? "暂未填写" : $supplier->address;
            $provinceName = $dictionaryService->getAreaNameByCodes($supplier->provinceCode);
            $provinceNameArray = empty($provinceName) ? [] : [$provinceName];
            $cityName = $dictionaryService->getAreaNameByCodes($supplier->cityCode);
            $cityNameArray = empty($cityName) ? [] : [$cityName];
            $basicInfo['regLocationCity'] = array_merge($provinceNameArray, $cityNameArray);
            //分支机构所在地
            $basicInfo['branchAddress'] = $this->getBranchAddress($supplier_id);
            //企业荣誉数量
            $basicInfo['honorCount'] = $this->getSupplierAwardAccount($supplier_id);
            $basicInfo['state'] = $supplier['state'];
            $basicInfo['status'] = $supplier['status'];

            $brandData = $this->getSupplierBrand($supplier_id);
            $basicInfo['brand'] = $brandData['brand'] ?? '';
            $basicInfo['brandStartTime'] = $brandData['start_time'] ? strtotime($brandData['start_time']) : 0;
            $basicInfo['brandEndTime'] = $brandData['end_time'] ? strtotime($brandData['end_time']) : 0;
            $basicInfo['companyLogo'] = $supplier->companyLogo ?? "";
            $basicInfo['listedType'] = $supplier->listedType;
            return $basicInfo;
        }
        return null;
    }

    /**
     * 获取供应商分类信息
     * @param $supplier_id
     * @return array
     */
    public function getSupplierCategoryInfo($supplier_id)
    {
        $categoryInfo = [];

        $sql = <<<sql
            SELECT
	 		    category_code
 		    FROM
 		        b2b_company.uuc_supplier_service_category
 		    WHERE
 		        supplier_id = :supplierId
            ORDER BY 
                uid ASC
            LIMIT 5
sql;
        $sqlParams = [':supplierId' => $supplier_id];
        $categoryCodes = \Yii::$app->db->createCommand($sql, $sqlParams)->queryColumn();

        $categories = BaseUtil::getAllBasicAndOperationCategoriesByCodes($categoryCodes);
        $categories['categoryCodeRanks'] = BaseUtil::calCategoryCodeRank($categoryCodes);
        if (is_array($categories)) {
            foreach ($categories as $key => $value) {
                $categoryInfo[$key] = $value;
            }
        }
        return $categoryInfo;
    }

    public function getStorgeInfo($companyId = null)
    {
        $sql = "select count(DISTINCT company_id) cnt, GROUP_CONCAT(DISTINCT company_id order by created_time desc) companyIds from
                b2b_company.bid_developer_mysupplier where status=2 and is_del=0 ";

        $sqlParam = null;
        if (!empty($companyId)) {
            $sql = $sql . " and supplier_id=:companyId";
            $sqlParam = [":companyId" => $companyId];
        }

        $command = \Yii::$app->db->createCommand($sql, $sqlParam);
        return $command->queryOne();
    }

    /**
     * 获取供应商其他信息
     * @param $supplier_id
     * @return array
     */
    public function getSupplierOtherInfo($supplier_id)
    {
        $otherInfo = [];

        //金牌供应商关键词设置
        $otherInfo['supplierKeywords'] = $this->getSupplierKeyWords($supplier_id);
        //获取活跃度得分
        $otherInfo['livenessScore'] = $this->getSupplierLiveScore($supplier_id);

        return $otherInfo;
    }

    /**
     * 获取供应商被推荐数
     * @param $supplier_id
     * @return int|string
     */
    public function getSupplierRecommend($supplier_id)
    {
        $model = new SupplierRecommendLog();
        $count = $model->find()->where(['supplier_id' => $supplier_id, 'is_cancle' => 2])->count('DISTINCT created_by');
        return $count;
    }

    /**
     * 新增供应商被推荐数
     * @param $dc_supplier_id
     * @param int $type
     * @return int
     */
    public function getRecommendLogsCount($dc_supplier_id, $type = 0)
    {
        $query = (new \yii\db\Query())->select('a.developer_id,c.company_name,c.company_logo,b.name,a.created_by')->distinct()
            ->from('b2b_data_center.uuc_supplier_recommend_log a')
            ->innerJoin('b2b_user_business.business_card_record b', 'b.user_id = a.created_by')
            ->innerJoin('b2b_company.uuc_developer c', 'c.developer_id = a.developer_id')
            ->where(['a.is_cancle' => 2, 'a.dc_supplier_id' => $dc_supplier_id])
            ->andWhere('c.company_name NOT LIKE "test%"');
        if (!empty($type)) {
            $query->andWhere(['a.type' => $type]);
        }
        return (int)$query->count();
    }

    /**
     * 获取供应商被入库数
     * @param $supplier_id
     * @return int
     */
    public function getSupplierInStorage($supplier_id)
    {
        if (empty($supplier_id)) {
            return null;
        }
//        $sql="SELECT
//				COUNT(DISTINCT dev.developer_id) as cnt
//                from
//                    b2b_company.bid_developer_mysupplier tm
//                    INNER JOIN b2b_company.uuc_developer  dev on dev.developer_id = tm.company_id
//                where  tm.supplier_id =:supplier_id
//                       and tm.STATUS=2 and  tm.is_del = 0
//                       and dev.company_name not like  'test_%'";

        $sql = "SELECT count(DISTINCT a.company_id) as cnt
            FROM b2b_company.bid_developer_mysupplier a
            INNER join b2b_company.uuc_developer b on a.company_id=b.developer_id
            where  a.supplier_id=:supplier_id and a.status=2 and a.is_del=0 and b.company_name not like 'test_%'
        and ((a.source = -1 and a.audit_status = 2 and a.audit_time is not null) or (a.source != -1)) ";

        $count = \Yii::$app->db->createCommand($sql, [":supplier_id" => $supplier_id])->queryScalar();
        return (int)$count;
    }

    public function getSupplierRegRecruitInfo($supplierId)
    {
        if (empty($supplierId)) {
            return [];
        }
        $sql = "select r.created_by,c.name,c.postion,case when r.created_by!='' then c.mobile else re.register_user_mobile end as mobile,count(1) as cnt 
            from b2b_bidding.bid_recruit_item_register r 
            left join b2b_bidding.bid_recruit_item_register_extend re on r.register_id=re.register_id
            left join b2b_user_business.business_card_record c on (r.created_by=c.user_id and r.created_by!='')
            where supplier_id=:supplier_id and r.created_time>DATE_SUB(CURDATE(), INTERVAL 1 YEAR) group by mobile order by cnt desc";
        $data = \Yii::$app->db->createCommand($sql, [":supplier_id" => $supplierId])->queryAll();
        if (empty($data)) {
            return [];
        }
        $result = ['regRecruitCount'=>0, 'recruitUserInfo'=>[]];
        $recruitUserInfo = ['userId'=>'', 'name'=>'', 'mobile'=>'', 'position'=>''];
        foreach ($data as $dv) {
            $result['regRecruitCount'] += $dv['cnt'];
        }
        $recruitUserInfo['mobile'] = $data[0]['mobile']??'';
        if (!empty($data[0]['created_by'])) {
            $recruitUserInfo['userId'] = $data[0]['created_by'];
            $recruitUserInfo['name'] = $data[0]['name']??'';
            $recruitUserInfo['position'] = $data[0]['postion']??'';
        } elseif (!empty($recruitUserInfo['mobile'])) {
            //查询1v1推荐的信息
            $sql = "select supplier_user_id,supplier_user_name,supplier_user_position from b2b_custom_success.uuc_demand_recommend 
                    where supplier_id=:supplier_id and supplier_user_mobile=:mobile limit 1";
            $recommendData = \Yii::$app->db->createCommand($sql, [":supplier_id" => $supplierId,":mobile"=>$recruitUserInfo['mobile']])->queryOne();
            if (!empty($recommendData)) {
                $recruitUserInfo['userId'] = $recommendData['supplier_user_id'];
                $recruitUserInfo['name'] = $recommendData['supplier_user_name']??'';
                $recruitUserInfo['position'] = $recommendData['supplier_user_position']??'';
            }
        }
        $result['recruitUserInfo'] = $recruitUserInfo;
        return $result;
    }

    /**
     * 获取供应商活跃度分数
     * @param $supplerId
     * @return int
     */
    private function getSupplierLiveScore($supplerId)
    {
        $score = [];
        //招标报名次数
        $score['biddingRegistration'] = $this->getLast30DayBiddingCount($supplerId);
        //招募报名次数
        $score['recruitRegistration'] = $this->getLast30DayRecruitCount($supplerId);
        //登录次数
        $score['loginNumbers'] = $this->getLast30DayLoginCount($supplerId);

        $liveItems = $this->getLiveScoreItemsInfo();
        $totalLiveScore = 0;
        foreach ($liveItems as $item) {
            if (array_key_exists($item['item'], $score)) {
                $tempScore = $item['score'] * $score[$item['item']];
                if ($tempScore > $item['max_score']) {
                    $totalLiveScore += $item['max_score'];
                } else {
                    $totalLiveScore += $tempScore;
                }
            }
        }
        $totalLiveScore = $totalLiveScore + mt_rand(0, 100) / 100;

        return $totalLiveScore;
    }

    /**
     * 获取最近30天公司所有人的登录次数
     * @param $supplier_id
     * @return int
     */
    private function getLast30DayLoginCount($supplier_id)
    {
        $totalCount = 0;
        $userService = new UserService();
        $userIdentities = $userService->getUserIdentityByCompanyId($supplier_id);
        foreach ($userIdentities as $userIdentity) {
            if (!empty($userIdentity) && !empty($userIdentity->userId)) {
                $userId = $userIdentity->userId;
                $sql = <<<sql
                    SELECT
                        COUNT(*)
                    FROM (
                        SELECT COUNT(1)
                        FROM b2b_user.uuc_user_login_log t1
                        WHERE t1.user_id=:userId   AND  t1.login_time >DATE_ADD(NOW(),INTERVAL -30  DAY
                    )
                    GROUP BY
                        DATE(t1.login_time) ) tmp
sql;
                $sqlParams = [':userId' => $userId];
                $count = \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();
                if ($totalCount < $count) {
                    $totalCount = $count;
                }
            }
        }
        return $totalCount;
    }

    /**
     * 获取最近30天招标报名次数
     * @param $supplier_id
     * @return false|null|string
     */
    private function getLast30DayBiddingCount($supplier_id)
    {
        $sql = <<<sql
            SELECT
                Count(1)
            FROM
                b2b_bidding.bid_announcements_register a
            INNER JOIN b2b_bidding.bid_announcements b ON b.uid = a.bid_announcement_uid
            INNER JOIN b2b_company.uuc_developer d ON b.developer_uid = d.developer_id
            WHERE
                a.supplier_id = :supplierId and a.register_date >= date_sub(CURDATE(),interval 30 day)
sql;
        $sqlParams = [':supplierId' => $supplier_id];
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();
    }

    /**
     * 返回活跃度积分项
     * @return array
     */
    private function getLiveScoreItemsInfo()
    {
        $sql = <<<sql
            SELECT
                item,
                score,
                max_score
            FROM
                b2b_search.supplier_liveness_score_item
sql;
        return \Yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * 获取最近30天招募报名次数
     * @param $supplier_id
     * @return false|null|string
     */
    private function getLast30DayRecruitCount($supplier_id)
    {
        $sql = <<<sql
            SELECT
                Count(1)
            FROM
                b2b_bidding.bid_recruit_item_register t1
            INNER JOIN b2b_bidding.bid_recruit_item t2 ON t2.item_id = t1.item_id
            INNER JOIN b2b_company.uuc_developer t3 ON t2.company_id = t3.developer_id
            WHERE
                t1.supplier_id = :supplierId
                and t1.created_time >= date_sub(CURDATE(),interval 30 day)
sql;
        $sqlParams = [':supplierId' => $supplier_id];
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();
    }

    /**
     * 获取金牌供应商搜索关键字
     * @param $supplier_id
     * @return array|false
     */
    private function getSupplierKeyWords($supplier_id)
    {
        $sql = <<<sql
            SELECT
                s.key_word1 as keyWord1,
                s.key_word2 as keyWord2,
                s.key_word3 as keyWord3
            FROM
                b2b_search.supplier_key_word s
            WHERE
                s.supplier_id = :supplierId
            AND s.`status`=1
sql;
        $sqlParams[':supplierId'] = $supplier_id;
        $keywords = \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();

        return empty($keywords) ? [] : $keywords;
    }

    /**
     * 获取供应商服务资历
     * @param $supplier_id
     * @return array
     */
    public function getSupplierQualifies($supplier_id)
    {
        $sql = <<<sql
            SELECT
                tb.qualify_name_code as qualifyCode,
                tb.qualify_level_code as qualifyLevelCode
            FROM
                b2b_company.uuc_supplier_qualify ta
            LEFT JOIN
                b2b_company.uuc_supplier_qualify_code tb ON ta.uid = tb.qualify_id
            WHERE
                ta.company_id = :companyId
                AND ta.`status` = 2 AND ta.is_del = 0
            ORDER BY
                ta.created_time DESC
sql;
        $sqlParams[':companyId'] = $supplier_id;
        $qualifies = \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();

        $qualifyInfo = [];
        if (!empty($qualifies)) {
            $quaNameArray = [];

            $quaService = new QualificationService();
            $quaCodeArray = array_column($qualifies, 'qualifyCode');
            $quaLevelCodeArray = array_column($qualifies, 'qualifyLevelCode');
            $quaLevelCodeArray = array_unique($quaLevelCodeArray);
            $qualifyLevelList = $quaService->getQualificationLevelList(['level_code' => $quaLevelCodeArray]);
            $qualifyLevelList = ArrayHelper::index($qualifyLevelList, 'level_code');

            foreach ($qualifies as $qualify) {
                if (empty($qualify['qualifyCode']) || empty($qualify['qualifyLevelCode'])) {
                    continue;
                }
                if ($qualifyLevelList && !empty($qualify['qualifyLevelCode'])) {
                    $qualifyLevel = isset($qualifyLevelList[$qualify['qualifyLevelCode']]) ? $qualifyLevelList[$qualify['qualifyLevelCode']] : '';

                    /** @var QualificationLevelModel $qualifyLevel */
                    $qualifyInfo['qualification_' . $qualify['qualifyCode']] = !empty($qualifyLevel) ? $qualifyLevel['priority'] : 0;
                    //$qualifyInfo['qualificationCode_Level'] = $qualify['qualifyLevelCode'];
                    $qualifyName = $quaService->getQualificationNameByCodesV2($qualify['qualifyCode']);
                    $qualifyLevelName = !empty($qualifyLevel) ? $qualifyLevel['level_name'] : '';
                    $quaNameArray[] = $qualifyName . $qualifyLevelName;
                }
            }
            $qualifyInfo['qualificationCode'] = $quaCodeArray;
            $qualifyInfo['qualificationName'] = $quaNameArray;
        }

        $sql = <<<sql
            SELECT
                COUNT(1)
            FROM
                b2b_company.uuc_supplier_qualify ta
            WHERE
                ta.company_id = :companyId AND
                ta.`status` = 2 AND
                ta.is_del = 0
sql;
        $sqlParams[':companyId'] = $supplier_id;
        $qualifyInfo['qualificationCount'] = \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();

        return $qualifyInfo;
    }

    /**
     * 通过供应商ID获取供应商的省市codes
     * @param $supplier_id
     * @return array
     */
    private function getServiceLocationCodeListByCompanyId($supplier_id)
    {
        $sql = <<<sql
            SELECT
                region_code
            FROM
                b2b_company.uuc_supplier_ser_area ta
            WHERE
                ta.supplier_id = :companyId
            ORDER BY
                ta.last_updated_time DESC
sql;
        $sqlParams[':companyId'] = $supplier_id;

        $regionInfo = \Yii::$app->db->createCommand($sql, $sqlParams)->queryColumn();

        return $regionInfo;
    }

    /**
     * 根据供应商ID获取供应商档案
     * @param $supplier_id
     * @return SupplierIntroduction
     */
    public function getSupplierIntroductionById($supplier_id)
    {
        $query = new Query();
        $model = new SupplierIntroduction();
        $data = $query->from(['b2b_company', 'supplierIntro'])->andWhere(["_id" => $supplier_id])->one();
        if (!empty($data)) {
            $data["supplierId"] = $data["_id"];
            unset($data["_id"]);
            $model->loadAttr($data);
        }
        return $model;
    }

    /**
     * 获取供应商的用户信息(金牌供应商取管理员，非金牌供应商取分数最高的)
     * @param $supplier_id
     * @return array
     */
    public function getSupplierUser($supplier_id)
    {
        //2.获取所有绑定的用户
        $userIdentities = null;
        $userService = new UserService();
        $userIdentities = $userService->getUserIdentityByCompanyId($supplier_id);
        if (empty($userIdentities)) {
            return [];
        }

        //2.获取所有用户信息
        $allUserInfo = [];
        foreach ($userIdentities as $userIdentity) {
            if (!isset($userIdentity->user_id) || empty($userIdentity->user_id)) {
                continue;
            }
            $userId = $userIdentity->user_id;
            /** @var SupplierHonourScoreModel $honourScore */
            $honourScore = SupplierHonourScoreModel::find()->where(['user_id' => $userId])->one();
            $userInfo = [];
            $userInfo['userId'] = $userId;
            $userInfo['score'] = !empty($honourScore) ? $honourScore->total_score : '';
            $userInfo['activeCount'] = !empty($honourScore) ? $honourScore->week_login_count : '';
            $userInfo['awardBidCount'] = !empty($honourScore) ? $honourScore->bid_count : '';
            $userInfo['inStorageCount'] = !empty($honourScore) ? $honourScore->in_storage_count : '';
            $userInfo['dataCount'] = !empty($honourScore) ? $this->getDataLevel($honourScore->data_count) : '';
            $userInfo['dataScore'] = !empty($honourScore) ? $honourScore->data_count : '';
            $userInfo['honorLevel'] = !empty($honourScore) ? $this->getLevel($honourScore->in_storage_count) : '';
            $userInfo['transactionLevel'] = !empty($honourScore) ? $this->getLevel($honourScore->bid_count) : '';
            if (!empty($honourScore) && ($honourScore->company_cert_type == 1 || $honourScore->company_cert_type == 2)) {
                $userInfo['identityAuthentication'] = false;
                $userInfo['companyAuthentication'] = true;
            } elseif (!empty($honourScore) && $honourScore->company_cert_type == 3) {
                $userInfo['identityAuthentication'] = true;
                $userInfo['companyAuthentication'] = false;
            } else {
                $userInfo['identityAuthentication'] = false;
                $userInfo['companyAuthentication'] = false;
            }

            $businessCard = BusinessCardModel::find()->where(["user_id" => $userId])->one();
            $userInfo['userName'] = !empty($businessCard) ? $businessCard->name : '';
            $userInfo['position'] = !empty($businessCard) ? $businessCard->postion : '';
            $userInfo['headPhoto'] = !empty($businessCard) ? $businessCard->logo : '';

            array_push($allUserInfo, $userInfo);
        }
        $data = count($allUserInfo) > 0 ? $allUserInfo : [];
        return ['userJSON' => json_encode($data)];
    }

    /**
     * 获取供应商成功案例
     * @param $supplier_id
     * @return array
     */
    public function getSupplierProjects($supplier_id)
    {
        $projectInfo = [];

        $projectList = $this->findProjectByCompanyId($supplier_id);
        $projectNameArray = $projectLocationCodeArray =
        $projectLocationNameArray = $projectDeveloperNameArray = $projectCategoryCodeArray = $projectCategoryNameArray = [];
        $dictionaryService = new DictionaryService();
        $categoryService = new BasicCategoryService();
        if (!empty($projectList)) {
            foreach ($projectList as $item) {
                $item['categoryCode'] = !empty($item['categoryCode']) ? explode(',', $item['categoryCode']) : [];
                !empty($item['projectName']) && array_push($projectNameArray, $item['projectName']);
                !empty($item['projectCompany']) && array_push($projectDeveloperNameArray, $item['projectCompany']);
//                !empty($item['categoryCode']) && array_push($projectCategoryCodeArray, $item['categoryCode']);
                !empty($item['categoryCode']) && $projectCategoryCodeArray = array_merge($projectCategoryCodeArray, $item['categoryCode']);
                $categoryCode = isset($item['categoryCode'][0]) ? $item['categoryCode'][0] : null;
                $categoryName = $categoryService->getCategoryNameByCode($categoryCode);
//                $categoryName = $categoryService->getCategoryNameByCode($item['categoryCode']);
                !empty($categoryName) && array_push($projectCategoryNameArray, $categoryName);
                !empty($item['provinceCode']) && array_push($projectLocationCodeArray, $item['provinceCode']);
                !empty($item['cityCode']) && array_push($projectLocationCodeArray, $item['cityCode']);
                $provinceName = $dictionaryService->getAreaNameByCodes($item['provinceCode']);
                $cityName = $dictionaryService->getAreaNameByCodes($item['cityCode']);
                !empty($provinceName) && array_push($projectLocationNameArray, $provinceName);
                !empty($cityName) && array_push($projectLocationNameArray, $cityName);
            }
        }

        $projectInfo['projectName'] = $projectNameArray;
        $projectInfo['projectDeveloperName'] = $projectDeveloperNameArray;
        $projectInfo['projectLocationCode'] = $projectLocationCodeArray;
        $projectInfo['projectLocationName'] = $projectLocationNameArray;
        $projectInfo['projectCategoryCode'] = $projectCategoryCodeArray;
        $projectInfo['projectCategoryName'] = $projectCategoryNameArray;

        //去重
        foreach ($projectInfo as $key => &$item) {
            if ($key == 'projectLocationName') { // 项目所在地不去重
                continue;
            }
            //array_flip(): only flip STRING and INTEGER values
            if (!empty($item) && is_array($item)) {
                $item = array_flip($item);
                $item = array_flip($item);
                $item = array_values($item);
            }
        }

        //案例数量
        $sql = <<<sql
            SELECT
                COUNT(1)
            FROM
                b2b_company.uuc_supplier_project ta
            WHERE
                ta. STATUS = 2
            AND ta.is_published = 1
            AND ta.is_del = 0
            AND ta.company_id = :companyId
sql;
        $sqlParams = ['companyId' => $supplier_id];
        $projectInfo['projectCount'] = (int)\Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();

        return $projectInfo;
    }

    /**
     * 获取供应商中标信息
     * @param $supplier_id
     * @return array
     */
    public function getSupplierAwardBidding($supplier_id)
    {
        $awardBiddingName = $awardBidDeveloperName = $awardBidProjectLocationCode =
        $awardBidProjectLocationName = $awardBidCategoryCode = $awardBidCategoryName = [];

        //1. 获取平台中标信息
        $sql = <<<sql
            SELECT
                a.bid_announcement_uid ,
                b.title ,
                f.project_id ,
                c.company_name,
                GROUP_CONCAT(e.category_name SEPARATOR ',') AS categoryName ,
                GROUP_CONCAT(d.category_code SEPARATOR ',') AS categoryCode ,
                GROUP_CONCAT(DISTINCT g.province_code SEPARATOR ',') AS provinceCode,
                GROUP_CONCAT(DISTINCT g.city_code SEPARATOR ',') AS cityCode
            FROM
                b2b_bidding.bid_announcements_register a
            INNER JOIN
                b2b_bidding.bid_announcements b ON a.bid_announcement_uid = b.uid AND b.is_delete = 0
            INNER JOIN
                b2b_company.uuc_developer c ON b.developer_uid = c.developer_id
            LEFT JOIN
                b2b_bidding.bid_announcement_categories d ON a.bid_announcement_uid = d.bid_announcement_uid
            LEFT JOIN
                b2b_parameter.bsp_basic_forest e ON d.category_code = e.category_code
            LEFT JOIN
                b2b_bidding.bid_announcements_project f ON a.bid_announcement_uid = f.bid_announcement_uid
            LEFT JOIN
                b2b_bidding.bid_announcements_area g ON b.uid = g.bid_announcement_uid
            WHERE
                a.supplier_id = :supplierId AND
                a.state = 25 AND
                b.is_delete = 0
            GROUP BY
                a.bid_announcement_uid
            ORDER BY
                b.complete_winbid_time DESC
sql;
        $sqlParams = [":supplierId" => $supplier_id];
        $platformBiddingInfo = \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();
        $dictionaryService = new DictionaryService();
        foreach ($platformBiddingInfo as $item) {
            array_push($awardBiddingName, $item['title']);
            array_push($awardBidDeveloperName, !empty($item['company_name']) ? $item['company_name'] : "");
            array_push($awardBidCategoryCode, $item['categoryCode']);
            array_push($awardBidCategoryName, $item['categoryName']);
            array_push($awardBidProjectLocationCode, $item['provinceCode']);
            array_push($awardBidProjectLocationCode, $item['cityCode']);
            $provinceName = $dictionaryService->getAreaNameByCodes($item['provinceCode']);
            array_push($awardBidProjectLocationName, $provinceName);
            $cityName = $dictionaryService->getAreaNameByCodes($item['cityCode']);
            array_push($awardBidProjectLocationName, $cityName);
        }

        //2. 获取用户发布的中标信息
        $sql2 = <<<sql
            SELECT
                win.winbid_project,
                win.project_province,
                win.project_city,
                win.service_category,
                win.service_category_name,
                win.developer_name
            FROM
                b2b_company.uuc_supplier_winbid_info win
            INNER JOIN
                b2b_user.uuc_user uu ON win.create_by = uu.user_id
            WHERE
                win.supplier_id = :supplierId AND
                win.is_deleted = 0
sql;
        $sqlParams2 = [":supplierId" => $supplier_id];
        $ownBiddingInfo = \Yii::$app->db->createCommand($sql2, $sqlParams2)->queryAll();
        foreach ($ownBiddingInfo as $item) {
            array_push($awardBiddingName, $item['winbid_project']);
            array_push($awardBidDeveloperName, $item['developer_name']);
            array_push($awardBidCategoryCode, $item['service_category']);
            array_push($awardBidCategoryName, $item['service_category_name']);
            array_push($awardBidProjectLocationCode, $item['project_province']);
            array_push($awardBidProjectLocationCode, $item['project_city']);
            $provinceName = $dictionaryService->getAreaNameByCodes($item['project_province']);
            array_push($awardBidProjectLocationName, $provinceName);
            $cityName = $dictionaryService->getAreaNameByCodes($item['project_city']);
            array_push($awardBidProjectLocationName, $cityName);
        }

        $awardBiddingInfo = [];
        $awardBiddingInfo['awardBidName'] = $awardBiddingName;
        $awardBiddingInfo['awardBidDeveloperName'] = $awardBidDeveloperName;
        $awardBiddingInfo['awardBidCount'] = count($platformBiddingInfo) + count($ownBiddingInfo);
        $awardBiddingInfo['awardBidProjectLocationCode'] = $awardBidProjectLocationCode;
        $awardBiddingInfo['awardBidProjectLocationName'] = $awardBidProjectLocationName;
        $awardBiddingInfo['awardBidCategoryCode'] = $awardBidCategoryCode;
        $awardBiddingInfo['awardBidCategoryName'] = $awardBidCategoryName;

        return $awardBiddingInfo;
    }

    /**
     * 获取产品信息
     * @param $supplier_id
     * @return array
     */
    public function getSupplierProducts($supplier_id)
    {
        $productNameArray = [];
        $productCount = 0;
        $list = $this->getProductsByCompanyId($supplier_id);
        if (!empty($list)) {
            foreach ($list as $item) {
                array_push($productNameArray, $item['title']);
                $productCount++;
            }
        }
        return ['productCount' => $productCount, 'productName' => $productNameArray];
    }

    /**
     * 获取供应商排序相关信息
     * @param $supplier_id
     * @param $basicInfo
     * @return array
     */
    public function getSupplierSortInfo($supplier_id, $basicInfo)
    {
        $sortInfo = [
            'isStoraged' => false,
            'hasEnoughRegCapital' => false,
            'hasEnoughContactors' => false,
            'hasEnoughProducts' => false,
        ];

        //是否被入库
        $supplierService = new SupplierService();
        $storageInfo = $supplierService->getStorgeInfo($supplier_id);
        if (!empty($storageInfo) && isset($storageInfo['cnt']) && $storageInfo['cnt'] > 0) {
            $sortInfo['isStoraged'] = true;
        }
        //注册资金是否大于1000w
        if (!empty($basicInfo) && isset($basicInfo['regCapital']) && $basicInfo['regCapital'] >= 1000) {
            $sortInfo['hasEnoughRegCapital'] = true;
        }
        //联系人是否大于3个
        $userService = new UserService();
        $userIdentities = $userService->getUserIdentityByCompanyId($supplier_id);
        if (!empty($userIdentities) && count($userIdentities) >= 3) {
            $sortInfo['hasEnoughContactors'] = true;
        }
        if (!empty($userIdentities) && count($userIdentities) >= 1) {
            $sortInfo['isCertificationCompany'] = 1;
        } else {
            $sortInfo['isCertificationCompany'] = 0;
        }
        //是否有产品
        $productList = $this->getProductsByCompanyId($supplier_id, 0, 1000);
        if (!empty($productList) && count($productList) > 0) {
            $sortInfo['hasEnoughProducts'] = true;
        }

        return $sortInfo;
    }

    /**
     * 得到该供应商的相关产品
     * @param $companyId
     * @return array
     */
    private function getProductsByCompanyId($companyId)
    {
        $sql = <<<sql
        SELECT
			pt.uid,
		    pt.title
		FROM
			b2b_company.uuc_product pt
		LEFT JOIN
		    b2b_company.uuc_personal_service_category sc On pt.uid = sc.target_id and sc.target_type = 1
		WHERE
		 	pt.company_id = :companyId
		 	AND pt.is_del = 0
		 	AND pt.putaway_status = 1
		 	AND pt.status = 2
sql;
        $sqlParams[':companyId'] = $companyId;
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();
    }

    /**
     * 查找该供应商的项目案例
     * @param $companyId
     * @return array
     */
    private function findProjectByCompanyId($companyId)
    {
        $sql = <<<sql
            SELECT
                ta.project_title as projectName,
                ta.uid as id,
                ta.project_remark as projectRemark,
                ta.company_id as companyId,
                ta.project_developer_name as projectCompany,
                ta.category_code as categoryCode,
                ta.project_province_code as provinceCode,
                ta.project_city_code as cityCode
            FROM
                b2b_company.uuc_supplier_project ta
            WHERE
                ta.status = 2 AND ta.is_published=1 AND ta.is_del = 0 AND
                ta.company_id =:companyId 
            ORDER BY
                ta.project_name DESC
sql;
        $sqlParams[':companyId'] = $companyId;

        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();
    }

    /**
     * 获取分数级别
     * @param $score
     * @return float|int
     */
    private function getDataLevel($score)
    {
        $completeLevel = 0;
        if ($score >= 60 && $score < 100) {
            $completeLevel = floor($score / 10);
        } else {
            if ($score >= 100) {
                $completeLevel = 10;
            }
        }
        return $completeLevel * 10;
    }

    /**
     * 判断入库次数或者中标次数的等级
     * @param $count
     * @return int
     */
    private function getLevel($count)
    {
        if ($count <= 0) {
            return 0;
        } else {
            if ($count <= 5) {
                return 1;
            } else {
                if ($count <= 15) {
                    return 2;
                } else {
                    if ($count <= 30) {
                        return 3;
                    } else {
                        if ($count <= 50) {
                            return 4;
                        }
                    }
                }
            }
        }
        return 5;
    }

    /**
     * 获取供应商品牌词
     * @param $supplier_id
     * @return string
     */
    private function getSupplierBrand($supplier_id)
    {
        $sql = <<<sql
            SELECT
                A.brand,
                B.start_time,
                B.end_time 
            FROM
                `b2b_company`.`uuc_supplier_brand` A
                INNER JOIN `b2b_company`.`uuc_supplier_document` B ON A.uid = B.relation_id 
                AND B.file_group = 3 
            WHERE
                A.company_id = :companyId
                AND A.created_time >= :createdTime
                AND A.status = 2
                AND A.is_del = 0
            LIMIT 1
sql;
        $sqlParam = [
            ':companyId' => $supplier_id,
            ':createdTime' => \Yii::$app->params['brand_extract_time'],
        ];
        return \Yii::$app->db->createCommand($sql, $sqlParam)->queryOne();
    }

    /**
     * 获取企业荣誉数量
     * @param $supplier_id
     * @return false|null|string
     */
    private function getSupplierAwardAccount($supplier_id)
    {
        $sql = <<<sql
            SELECT
                count(1)
            FROM
                b2b_company.uuc_supplier_award ta
            WHERE
                ta.company_id = :companyId AND
                ta.status = 2 AND
                ta.is_del = 0
sql;
        $sqlParam[':companyId'] = $supplier_id;
        return \Yii::$app->db->createCommand($sql, $sqlParam)->queryScalar();
    }

    /**
     * 获取供应商分支机构地址
     * @param $supplier_id
     * @return array
     */
    private function getBranchAddress($supplier_id)
    {
        $sql = <<<sql
            SELECT
                province_code,
                city_code
            FROM
                b2b_company.uuc_supplier_institution
            WHERE
                company_id = :supplierId AND
                is_del = 0 AND
                `status` = 2
sql;
        $sqlParams = [':supplierId' => $supplier_id];
        $data = \Yii::$app->db->createCommand($sql, $sqlParams)->queryAll();

        $branchAddress = [];
        $dictionaryService = new DictionaryService();
        foreach ($data as $item) {
            if (!empty($item) && !empty($item["province_code"])) {
                $provinceName = $dictionaryService->getAreaNameByCodes($item["province_code"]);
                !empty($provinceName) ? array_push($branchAddress, $provinceName) : null;
            }
            if (!empty($item) && !empty($item["city_code"])) {
                $cityName = $dictionaryService->getAreaNameByCodes($item["city_code"]);
                !empty($cityName) ? array_push($branchAddress, $cityName) : null;
            }
        }

        //去重
        foreach ($branchAddress as &$item) {
            //array_flip(): only flip STRING and INTEGER values
            if (!empty($item) && is_array($item)) {
                $item = array_flip($item);
                $item = array_flip($item);
            }
        }

        return $branchAddress;
    }

    /**
     * 获取行业数据
     * @param $regLocationCode
     * @param $regLocationCity
     * @param $companyName
     * @return array
     */
    public function getIndustryData($regLocationCode, $regLocationCity, $companyName)
    {
        $data = [];

        $data['provinceCode'] = !empty($regLocationCode[0]) ? $regLocationCode[0] : '';   //省份code
        $data['cityCode'] = !empty($regLocationCode[1]) ? $regLocationCode[1] : '';       //城市code

        $data['provinceName'] = !empty($regLocationCity[0]) ? $regLocationCity[0] : '';   //省份名称
        $data['cityName'] = !empty($regLocationCity[1]) ? $regLocationCity[1] : '';       //城市名称

        $condition = [
            'supplier_name' => $companyName,
        ];
        $fields = 'developer_name';
        $ycSupplierData = (new LandRepository())->getYcHundredSupplierData($condition, $fields);  //百强战略合作单位数据
        if (!empty($ycSupplierData)) {
            $data['hundredCount'] = count(array_unique(array_column($ycSupplierData, 'developer_name')));
//            $data['isHundred'] = true;
        } else {
            $data['hundredCount'] = 0;
//            $data['isHundred'] = false;
        }

        return $data;
    }

    /**
     * 获取供应商十强榜单
     * @param $supplierId
     * @return int[]
     */
    public function getSupplierVoteInfo($supplierId)
    {
        $result = [
            'isTop10For2020' => 0,
            'isHighQualityFor2020' => 0,
            'topTenCategory' => ''
        ];
        if (!empty($supplierId)) {
            // 2023-11 峰会榜单迭代，未开放榜单前先兼容历史年份数据
            $fhRandYear = SystemConfigService::get('fh_rank_year');
            $year = ($fhRandYear == '2023') ? $fhRandYear: '2020';
            $rows = UucSupplierVoteModel::findAll([
                'supplier_id' => $supplierId,
                'year' => $year
            ]);
            if (!empty($rows)) {
                $categoryName = array_unique(array_column($rows, 'category_name'));
                foreach ($categoryName as $cName) {
                    if (self::HIGH_QUALITY_FOR_2020_MARK == $cName) {
                        $result['isHighQualityFor2020'] = 1;
                    } else {
                        $result['isTop10For2020'] = 1;
                        $result['topTenCategory'] = implode('/', $categoryName);
                    }

                }
            }
        }

        return $result;
    }


    /**
     * 通过供应商ID返回公司旗下联系人数量
     * @param $supplierId
     * @param $basicInfo
     * @return int
     * @throws \yii\base\InvalidConfigException
     */
    public function getContactsCountBySupplierId($supplierId, $basicInfo)
    {
        if ($supplierId) {
            $isDiamond = $basicInfo['isDiamondSupplier'] ?? 0;
            $isRegionDiamond = $basicInfo['isRegionalDiamond'] ?? 0;
            $isGolden = $basicInfo['isGoldenSupplier'] ?? 0;
            if ($isDiamond == 1 || $isRegionDiamond == 1 || $isGolden == 1) { // 如果是【全国钻石】或【区域钻石】或【金牌】，则读取b2b_supplier.supplier_contact表数据
                $dbName = "dbSupplier";
                $sql = <<<sql
                  select user_id from b2b_supplier.supplier_contact where supplier_id = :supplier_id and is_show = 1 and is_deleted = 1
sql;
                $userIds = \Yii::$app->get($dbName)
                    ->createCommand($sql)
                    ->bindValue(':supplier_id', $supplierId)
                    ->queryColumn();

                if (count($userIds) > 0) {
                    foreach ($userIds as &$v){
                        $v = '"' . $v . '"';
                    }
                    $userStr = '(' . implode(',',$userIds) . ')';
                    $sql = "select count(1) from b2b_user.uuc_user WHERE user_id IN $userStr and `status` = 2";
                    $dbName = "db";
                    return \Yii::$app->get($dbName)
                        ->createCommand($sql)
                        ->queryScalar();
                }

            } else {
                $dbName = "db";
                $sql = <<<sql
                  select count(1) from b2b_user.uuc_user_identity a
                  left join b2b_user.uuc_user b on a.user_id = b.user_id
                  where company_id = :supplier_id and b.`status` = 2
sql;
                return \Yii::$app->get($dbName)
                    ->createCommand($sql)
                    ->bindValue(':supplier_id', $supplierId)
                    ->queryScalar();
            }
        }
        return 0;
    }

    /**
     * 根据公司名称获取百强战略供应商
     * @param $supplierNames
     * @return mixed
     * @throws \yii\base\InvalidConfigException
     */
    public function getTopStrategySupplierBySupplerNames($supplierNames)
    {
        $sql = <<<sql
            SELECT developer_name, short_name, supplier_name FROM b2b_data_center.youcai_hundred_supplier_data
sql;
        $condition = ' WHERE supplier_name in ("' . implode('","', $supplierNames) . '") group by developer_name';
        /** @var Connection $connection */
        $connection = \Yii::$app->get('db');
        $result = $connection->createCommand($sql . $condition)
            ->queryAll();
        return $result;
    }
}
